﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.IO;

namespace SQL_Test
{
    public class Program
    {
        static void ReadAllIntoDataBase()
        {


            //T002E004484\KBMSS

            SqlConnection s = new SqlConnection();





            //s.Database.Connection.ConnectionString =
            //    @"server=localhost;" +
            //    @"Database=main;" +
            //    //"Password=;" +
            //    "Trusted_Connection = True;" +
            //    "Integrated Security = True;"

            //    ;

            SqlConnection myConnection = new SqlConnection(
                                       @"Data Source=T002EOO4478\KBMSS;" +
                                       "Trusted_Connection= True;" +
                                       "Integrated Security=True;" +
                                       @"Initial Catalog=TypeOfJump;" +
                                       "connection timeout=5;" +
                                        "MultipleActiveResultSets = True;");


            try
            {
                myConnection.Open();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }



            //Denna skit funkar bra.
            //try
            //{
            //    SqlDataReader myReader = null;

            //    SqlCommand MyCommand = new SqlCommand("SELECT * FROM Table_1", myConnection);

            //    myReader = MyCommand.ExecuteReader();
            //    while (myReader.Read())
            //    {
            //        Console.WriteLine(myReader[0].ToString());
            //    }

            //    //MyCommand.EndExecuteNonQuery(
            //}
            //catch (Exception e)
            //{
            //    Console.WriteLine(e.ToString());
            //}

            //SqlCommand MyCommand1 = new SqlCommand("INSERT INTO Table_1(name) VALUES('hejsan')", myConnection);
            //Console.WriteLine(MyCommand1.ExecuteNonQuery());

            //Console.ReadKey();



            //Lägg till all bra skit i en bra tabell!

            Competition c = new Competition();


            //Loads all the posible jumps and their data into the list c.DivingTypeList
            c.LoadDivingTypesFromFile();


            for (int i = 0; i < c.DivingTypeList.Count; i++)
            {

                SqlCommand MyCommand = new SqlCommand("INSERT INTO JumpName(JumpID, TypeOfJump) VALUES('"
                                                        + c.DivingTypeList[i].ID + "','" + c.DivingTypeList[i].TypeName + "')", myConnection);

                MyCommand.ExecuteNonQuery();
                for (int j = 0; j < c.DivingTypeList[i].Height.Count; j++)
                {
                    //Console.WriteLine("INSERT INTO Table_1(ID, TypeOfJump, Height, Difficulty) VALUES('" + c.DivingTypeList[i].ID + "'" + "," + "'" + c.DivingTypeList[i].TypeName + "'," + c.DivingTypeList[i].Height[j] + "," + (float)c.DivingTypeList[i].DoublePointsMultiplier[j] + ")");
                    //SqlCommand MyCommand = new SqlCommand("INSERT INTO Table_1(ID, TypeOfJump, Height, Difficulty) VALUES('" + c.DivingTypeList[i].ID + "'" + "," + "'" + c.DivingTypeList[i].TypeName + "'," + c.DivingTypeList[i].Height[j] + "," + (float)c.DivingTypeList[i].DoublePointsMultiplier[j] + ")", myConnection);

                    SqlCommand MyCommand2 = new SqlCommand("INSERT INTO HeightsAndDifficulty(JumpID, Height, Difficulty) values('"
                                                            + c.DivingTypeList[i].ID + "','" + c.DivingTypeList[i].Height[j] + "','" + (float)c.DivingTypeList[i].DoublePointsMultiplier[j] + "')", myConnection);
                    MyCommand2.ExecuteNonQuery();
                    
                }

            }


            Console.WriteLine("ALLT INLÄST FRÅN FILEN OCH INLAGGT I DATABASEN");

        }


        static void TestReadFromSQL()
        {
            SqlConnection s = new SqlConnection();

            SqlConnection myConnection = new SqlConnection(
                                       @"Data Source=T002EOO4478\KBMSS;" +
                                       "Trusted_Connection= True;" +
                                       "Integrated Security=True;" +
                                       @"Initial Catalog=TypeOfJump;" +
                                       "connection timeout=5;" +
                                        "MultipleActiveResultSets = True;");

            myConnection.Open();

            SqlDataReader MyReader1 = null;
            SqlDataReader MyReader2 = null;

            String VadVillDu = "";

            Console.WriteLine("Skriv den skit som du vill veta saker om: ");
            VadVillDu = Console.ReadLine();

            Console.WriteLine();
            Console.WriteLine();
            Console.WriteLine("Information:");

            SqlCommand MyCommand1 = new SqlCommand("Select * from JumpName where JumpID = '" + VadVillDu + "'", myConnection);
            SqlCommand MyCommand2 = new SqlCommand("Select * from HeightsAndDifficulty where JumpID = '" + VadVillDu + "'", myConnection);

            MyReader1 = MyCommand1.ExecuteReader();
            MyReader2 = MyCommand2.ExecuteReader();
            while (MyReader1.Read())
            {
                Console.WriteLine(MyReader1[0].ToString());
                Console.WriteLine(MyReader1[1].ToString());

                Console.WriteLine();

                while (MyReader2.Read())
                {
                    //Console.WriteLine(MyReader2[0].ToString());
                    //Console.WriteLine(MyReader2[1].ToString());
                   
                    Console.WriteLine("Höjd:  " + MyReader2[1].ToString());
                    Console.WriteLine("Poäng: " + MyReader2[2].ToString());
                    Console.WriteLine("---------");
                }
            }
        }



        static void Main(string[] args)
        {

            //KÖR INTE DENNA FUNKTION OM DET REDAN FINNS DATA I TABELLERNA FÖR HOPP
            //ReadAllIntoDataBase();



            TestReadFromSQL();


            Console.ReadKey();
        }




    }
}

